﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using FbKPI.Model;
namespace FbKPI.Controller
{
    public class KPIAssignController : FbKPI.Controller.dbConnection.ConnectionClass
    {
        public string Save(KPIAssign KPIs)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_InsertUpdateKPIAssign";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KPIs.KPI.ID;
            command.Parameters.Add("@RGMID", SqlDbType.VarChar).Value = KPIs.RGM.ID;
            command.Parameters.Add("@LOCATIONID", SqlDbType.VarChar).Value = KPIs.Location == null ? 0 : KPIs.Location.ID;
            command.Parameters.Add("@SUBBRANCHID", SqlDbType.VarChar).Value = KPIs.SubBranch == null ? 0 : KPIs.SubBranch.ID;
            //command.Parameters.Add("@VERIFIER", SqlDbType.VarChar).Value = KPIs.Verifier.Supervisor.ID;
            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
            command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIs.Polarization;
            command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
            command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIs.UoMDetailMax == null ? null : KPIs.UoMDetailMax.ID.ToString();
            command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIs.UoMDetailMin == null ? null : KPIs.UoMDetailMin.ID.ToString();
            command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIs.ManualCalculation;
            command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIs.Weight;
            command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIs.WeightEnableFlag;
            command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIs.UoMEnableFlag;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;

            return ExecuteNonQuery(command);
            
        }


        public bool SaveWithList(List<KPIAssign> list)
        {
            bool result = true;

            string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    foreach (KPIAssign KPIs in list)
                    {
                        string query = "UP_InsertUpdateKPIAssign";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
                        command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KPIs.KPI.ID;
                        command.Parameters.Add("@RGMID", SqlDbType.VarChar).Value = KPIs.RGM.ID;
                        command.Parameters.Add("@LOCATIONID", SqlDbType.VarChar).Value = KPIs.Location == null ? 0 : KPIs.Location.ID;
                        command.Parameters.Add("@SUBBRANCHID", SqlDbType.VarChar).Value = KPIs.SubBranch == null ? 0 : KPIs.SubBranch.ID;
                        //command.Parameters.Add("@VERIFIER", SqlDbType.VarChar).Value = KPIs.Verifier.Supervisor.ID;
                        command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
                        command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIs.Polarization;
                        command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
                        command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIs.UoMDetailMax == null ? null : KPIs.UoMDetailMax.ID.ToString();
                        command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIs.UoMDetailMin == null ? null : KPIs.UoMDetailMin.ID.ToString();
                        command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIs.ManualCalculation;
                        command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIs.Weight;
                        command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIs.WeightEnableFlag;
                        command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIs.UoMEnableFlag;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
                        command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;

                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();
                    }

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public bool DeleteWithList(List<KPIAssign> list)
        {
            bool result = true;

            string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    foreach (KPIAssign KPIs in list)
                    {
                        string query = "UP_DeleteKPIAssign";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        //SqlCommand command = new SqlCommand();
                        //command.CommandType = CommandType.StoredProcedure;
                        //command.CommandText = "UP_DeleteKPIAssignDetail";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();
                    }

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public string Delete(KPIAssign KPIs)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_DeleteKPIAssign";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;

            return ExecuteNonQuery(command);
        }

        public IList<KPIAssign> GetAll(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee User)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIAssigns";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = User.ID;

            DataSet dx = GetTable(command);

            List<KPIAssign> KPIs = new List<KPIAssign>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "KPIDescription":
                            dx.Tables[0].DefaultView.Sort = "KPIID ASC";
                            break;
                        case "RGM":
                            dx.Tables[0].DefaultView.Sort = "RGMID ASC";
                            break;
                        case "Location":
                            dx.Tables[0].DefaultView.Sort = "LocationID ASC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID ASC";
                            break;
                        case "UoM":
                            dx.Tables[0].DefaultView.Sort = "UoMID ASC";
                            break;
                        case "Verifier":
                            dx.Tables[0].DefaultView.Sort = "Verifier ASC";
                            break;
                        case "LastModifiedDate":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
                            break;
                        case "LastModifiedBy":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
                            break;
                        case "CreateDate":
                            dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
                            break;
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "KPIDescription":
                            dx.Tables[0].DefaultView.Sort = "KPIID DESC";
                            break;
                        case "RGM":
                            dx.Tables[0].DefaultView.Sort = "RGMID DESC";
                            break;
                        case "Location":
                            dx.Tables[0].DefaultView.Sort = "LocationID DESC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID DESC";
                            break;
                        case "UoM":
                            dx.Tables[0].DefaultView.Sort = "UoMID DESC";
                            break;
                        case "Verifier":
                            dx.Tables[0].DefaultView.Sort = "Verifier DESC";
                            break;
                        case "LastModifiedDate":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
                            break;
                        case "LastModifiedBy":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
                            break;
                        case "CreateDate":
                            dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAssign p = new KPIAssign();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        p.RGM = new RGMController().GetRGMByID(Convert.ToInt32( dx.Tables[0].Rows[i]["RGMID"].ToString()));
                        p.Location = new LocationController().GetLocationByID(Convert.ToInt32(dx.Tables[0].Rows[i]["LocationID"].ToString()));
                        p.SubBranch = new SubBranchController().GetSubBranchByID(Convert.ToInt32(dx.Tables[0].Rows[i]["SubBranchID"].ToString()));
                        p.Weight = Convert.ToInt16(dx.Tables[0].Rows[i]["Weight"].ToString());
                        p.UoM = new UoMController().GetUoMByID(dx.Tables[0].Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMID"].ToString()));
                        p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMax"].ToString()));
                        p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMin"].ToString()));
                        p.WeightEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["UoMEnableFlag"].ToString());
                        p.Verifier = new EmployeeController().GetEmployeeByID(dx.Tables[0].Rows[i]["Verifier"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["Verifier"].ToString()));
                        p.Polarization = dx.Tables[0].Rows[i]["Polarization"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dx.Tables[0].Rows[i]["ManualCalculation"].ToString());
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dx.Tables[0].Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPIAssign> GetAllKPIAssignsByKPIID(string sSearch, int KPIID, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIAssignsByKPIID";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@KPIID", SqlDbType.Int).Value = KPIID;

            DataSet dx = GetTable(command);

            List<KPIAssign> KPIs = new List<KPIAssign>();

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAssign p = new KPIAssign();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        p.RGM = new RGMController().GetRGMByID(Convert.ToInt32(dx.Tables[0].Rows[i]["RGMID"].ToString()));
                        p.Location = new LocationController().GetLocationByID(Convert.ToInt32(dx.Tables[0].Rows[i]["LocationID"].ToString()));
                        p.SubBranch = new SubBranchController().GetSubBranchByID(Convert.ToInt32(dx.Tables[0].Rows[i]["SubBranchID"].ToString())); 
                        p.Weight = Convert.ToInt16(dx.Tables[0].Rows[i]["Weight"].ToString());
                        p.UoM = new UoMController().GetUoMByID(dx.Tables[0].Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMID"].ToString()));
                        p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMax"].ToString()));
                        p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMin"].ToString()));
                        p.WeightEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["UoMEnableFlag"].ToString());
                        p.Verifier = new EmployeeController().GetEmployeeByID(dx.Tables[0].Rows[i]["Verifier"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["Verifier"].ToString()));
                        p.Polarization = dx.Tables[0].Rows[i]["Polarization"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dx.Tables[0].Rows[i]["ManualCalculation"].ToString());
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dx.Tables[0].Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPIAssign> GetAllKPIAssignsByKPIID(int KPIID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIAssignsByKPIID";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = string.Empty;
            command.Parameters.Add("@KPIID", SqlDbType.Int).Value = KPIID;

            DataSet dx = GetTable(command);

            List<KPIAssign> KPIs = new List<KPIAssign>();

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAssign p = new KPIAssign();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        p.RGM = new RGMController().GetRGMByID(Convert.ToInt32(dx.Tables[0].Rows[i]["RGMID"].ToString()));
                        p.Location = new LocationController().GetLocationByID(Convert.ToInt32(dx.Tables[0].Rows[i]["LocationID"].ToString()));
                        p.SubBranch = new SubBranchController().GetSubBranchByID(Convert.ToInt32(dx.Tables[0].Rows[i]["SubBranchID"].ToString()));
                        p.Weight = Convert.ToInt16(dx.Tables[0].Rows[i]["Weight"].ToString());
                        p.UoM = new UoMController().GetUoMByID(dx.Tables[0].Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMID"].ToString()));
                        p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMax"].ToString()));
                        p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMin"].ToString()));
                        p.WeightEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["UoMEnableFlag"].ToString());
                        p.Verifier = new EmployeeController().GetEmployeeByID(dx.Tables[0].Rows[i]["Verifier"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["Verifier"].ToString()));
                        p.Polarization = dx.Tables[0].Rows[i]["Polarization"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dx.Tables[0].Rows[i]["ManualCalculation"].ToString());
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return KPIs.ToList();
        }

        public KPIAssign GetByKPIID(int KPIID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAssignsByKPIID";
            //command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = string.Empty;
            command.Parameters.Add("@KPIID", SqlDbType.Int).Value = KPIID;

            DataSet dx = GetTable(command);

            KPIAssign p = null;

            //List<KPIAssign> KPIs = new List<KPIAssign>();

            //DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dx.Tables[0].Rows.Count; i++)
                {
                    //if (i < dx.Tables[0].Rows.Count)
                    //{
                        p = new KPIAssign();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        p.RGM = new RGMController().GetRGMByID(Convert.ToInt32(dx.Tables[0].Rows[i]["RGMID"].ToString()));
                        p.Location = new LocationController().GetLocationByID(Convert.ToInt32(dx.Tables[0].Rows[i]["LocationID"].ToString()));
                        p.SubBranch = new SubBranchController().GetSubBranchByID(Convert.ToInt32(dx.Tables[0].Rows[i]["SubBranchID"].ToString()));
                        p.Weight = Convert.ToInt16(dx.Tables[0].Rows[i]["Weight"].ToString());
                        p.UoM = new UoMController().GetUoMByID(dx.Tables[0].Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMID"].ToString()));
                        p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMax"].ToString()));
                        p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMin"].ToString()));
                        p.WeightEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["UoMEnableFlag"].ToString());
                        p.Verifier = new EmployeeController().GetEmployeeByID(dx.Tables[0].Rows[i]["Verifier"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["Verifier"].ToString()));
                        p.Polarization = dx.Tables[0].Rows[i]["Polarization"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dx.Tables[0].Rows[i]["ManualCalculation"].ToString());
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        //KPIs.Add(p);
                    //}
                    //else
                    //{
                    //    break;
                    //}

                }
            }

            return p;// KPIs.ToList();
        }

        public KPIAssign GetKPIAssignByID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAssignByID";
            command.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

            IList<string> Data = GetSingleRow(command);
            KPIAssign KPIs = null;

            if (Data != null && Data.Count > 0)
            {
                KPIs = new KPIAssign();
                KPIs.ID = Convert.ToInt16(Data[0].ToString());
                KPIs.KPI = new KPIController().GetKPIByID(Convert.ToInt16(Data[1].ToString()));
                KPIs.RGM = new RGMController().GetRGMByID(Convert.ToInt32(Data[2].ToString()));
                KPIs.Location = new LocationController().GetLocationByID(Convert.ToInt32(Data[3].ToString()));
                KPIs.SubBranch = new SubBranchController().GetSubBranchByID(Convert.ToInt32(Data[4].ToString())); 
                KPIs.Weight = Convert.ToInt16(Data[5].ToString());
                KPIs.UoM = new UoMController().GetUoMByID(Data[6] == string.Empty ? 0 : Convert.ToInt16(Data[6].ToString()));
                KPIs.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(Data[7] == string.Empty ? 0 : Convert.ToInt16(Data[7].ToString()));
                KPIs.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(Data[8] == string.Empty ? 0 : Convert.ToInt16(Data[8].ToString()));
                KPIs.WeightEnableFlag = Convert.ToBoolean(Data[9].ToString());
                KPIs.UoMEnableFlag = Convert.ToBoolean(Data[10].ToString());
                KPIs.Verifier = new EmployeeController().GetEmployeeByID(Data[11] == string.Empty ? 0 : Convert.ToInt16(Data[11].ToString()));
                KPIs.Polarization = Data[12].ToString();
                KPIs.ManualCalculation = Convert.ToInt32(Data[13].ToString());
                KPIs.isActive = Convert.ToBoolean(Data[14].ToString());
                KPIs.LastModifiedDate = Data[15] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[15].ToString());
                KPIs.LastModifiedBy = Data[16].ToString(); 
                KPIs.CreateDate = Data[17] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[17].ToString());
                KPIs.CreateBy = Data[18].ToString();
            }

            return KPIs;
        }

        public bool ValidateWeight(Employee employees, KPIAssign Assign, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_ValidateWeight";
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = employees.ID;
            command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = Assign.Weight;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            return Convert.ToBoolean(GetSingleData(command));
        }

        public bool ValidateWeightDua(Employee employees, int Weight, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_ValidateWeight";
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = employees.ID;
            command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = Weight;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            return Convert.ToBoolean(GetSingleData(command));
        }
    }
}